﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using UserDefineForm.dao.orm;
using System.Data;
using UserDefineForm.exception;

namespace UserDefineForm.dao.supplierform
{
    public class ControlDAO
    {
        private SqlConnection sqlConnection;

        private ControlORM createControlORMFromReader(IDataReader reader)
        {
            ControlORM item = new ControlORM();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) item.Id = (int)reader["ID"];
                if (!reader.IsDBNull(reader.GetOrdinal("FormID"))) item.FormId = (int)reader["FormID"];
                if (!reader.IsDBNull(reader.GetOrdinal("ControlType"))) item.Type = (int)reader["ControlType"];
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) item.Name = (string)reader["Name"];
                if (!reader.IsDBNull(reader.GetOrdinal("Width"))) item.Width = (int)reader["Width"];
                if (!reader.IsDBNull(reader.GetOrdinal("Height"))) item.Height = (int)reader["Height"];
                if (!reader.IsDBNull(reader.GetOrdinal("LocationTop"))) item.Top = (int)reader["LocationTop"];
                if (!reader.IsDBNull(reader.GetOrdinal("LocationLeft"))) item.Left = (int)reader["LocationLeft"];
                if (!reader.IsDBNull(reader.GetOrdinal("Font"))) item.Font = (string)reader["Font"];
                if (!reader.IsDBNull(reader.GetOrdinal("DefaultValue"))) item.DefaultValue = (string)reader["DefaultValue"];
                if (!reader.IsDBNull(reader.GetOrdinal("ToolTipText"))) item.TooltipText = (string)reader["ToolTipText"];
                if (!reader.IsDBNull(reader.GetOrdinal("SummaryField"))) item.SummaryField = (int)reader["SummaryField"];
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) item.Status = (int)reader["Status"];
                if (!reader.IsDBNull(reader.GetOrdinal("ListTextID"))) item.ListIndexId = (int)reader["ListTextID"];
                if (!reader.IsDBNull(reader.GetOrdinal("Value"))) item.Value = (string)reader["Value"];


            }
            catch (Exception ex)
            {
                // wrap it and rethrow
                throw new DataReadException(ex.Message);
            }
            return item;
        }

        public ControlDAO(SqlConnection sqlConnection)
        {
            this.sqlConnection = sqlConnection;
        }

        public ControlORM getControlById(int id)
        {
            SqlDataReader reader=null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("GetControlById", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", id));

                reader = sqlCmd.ExecuteReader();
                if (reader.Read())
                {
                    ControlORM result = createControlORMFromReader(reader);
                    return result;
                }
                else
                {
                    return null;
                }
            }
            finally
            {
                if(sqlConnection!=null) sqlConnection.Close();
                if(reader != null) reader.Close();
            }
        }

        public void insertControl(ControlORM controlOrm)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("InsertControl", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", controlOrm.Id));
                sqlCmd.Parameters["@id"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add(new SqlParameter("@formId", controlOrm.FormId));
                sqlCmd.Parameters.Add(new SqlParameter("@controlType", controlOrm.Type));
                sqlCmd.Parameters.Add(new SqlParameter("@name", controlOrm.Name));
                sqlCmd.Parameters.Add(new SqlParameter("@width", controlOrm.Width));
                sqlCmd.Parameters.Add(new SqlParameter("@height", controlOrm.Height));
                sqlCmd.Parameters.Add(new SqlParameter("@top", controlOrm.Top));
                sqlCmd.Parameters.Add(new SqlParameter("@left", controlOrm.Left));
                sqlCmd.Parameters.Add(new SqlParameter("@font", controlOrm.Font));
                sqlCmd.Parameters.Add(new SqlParameter("@defaultValue", controlOrm.DefaultValue));
                sqlCmd.Parameters.Add(new SqlParameter("@tooltipText", controlOrm.TooltipText));
                sqlCmd.Parameters.Add(new SqlParameter("@summaryField", controlOrm.SummaryField));
                sqlCmd.Parameters.Add(new SqlParameter("@status", controlOrm.Status));
                sqlCmd.Parameters.Add(new SqlParameter("@listTextId", controlOrm.ListIndexId));
                sqlCmd.Parameters.Add(new SqlParameter("@value", controlOrm.Value));

                sqlCmd.ExecuteNonQuery();

                controlOrm.Id = (int)sqlCmd.Parameters["@id"].Value;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public void updateControl(ControlORM controlOrm)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("UpdateControl", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", controlOrm.Id));
                //sqlCmd.Parameters.Add(new SqlParameter("@formId", controlOrm.FormId));
                //sqlCmd.Parameters.Add(new SqlParameter("@controlType", controlOrm.ControlType));
                sqlCmd.Parameters.Add(new SqlParameter("@name", controlOrm.Name));
                sqlCmd.Parameters.Add(new SqlParameter("@width", controlOrm.Width));
                sqlCmd.Parameters.Add(new SqlParameter("@height", controlOrm.Height));
                sqlCmd.Parameters.Add(new SqlParameter("@top", controlOrm.Top));
                sqlCmd.Parameters.Add(new SqlParameter("@left", controlOrm.Left));
                sqlCmd.Parameters.Add(new SqlParameter("@font", controlOrm.Font));
                sqlCmd.Parameters.Add(new SqlParameter("@defaultValue", controlOrm.DefaultValue));
                sqlCmd.Parameters.Add(new SqlParameter("@tooltipText", controlOrm.TooltipText));
                sqlCmd.Parameters.Add(new SqlParameter("@summaryField", controlOrm.SummaryField));
                sqlCmd.Parameters.Add(new SqlParameter("@status", controlOrm.Status));
                sqlCmd.Parameters.Add(new SqlParameter("@listTextId", controlOrm.ListIndexId));
                sqlCmd.Parameters.Add(new SqlParameter("@value", controlOrm.Value));

                sqlCmd.ExecuteNonQuery();

            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }

        }

        public List<ControlORM> getAllControlsByFormID(int formID)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("GetAllControlsByFormID", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@formId", formID));

                reader = sqlCmd.ExecuteReader();

                List<ControlORM> result = new List<ControlORM>();

                while (reader.Read())
                {
                    ControlORM control = createControlORMFromReader(reader);
                    result.Add(control);
                }
                return result;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public void deleteControl(int id)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("DeleteControl", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", id));

                sqlCmd.ExecuteNonQuery();

            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }
    }
}
